package com.design.working.dao.impl;

import com.design.working.bo.HandleDeptJobApplicationBO;
import com.design.working.dao.AdminDAO;
import com.design.working.dto.*;
import com.design.working.po.admin.*;
import com.design.working.util.JDBCUtil;
import com.design.working.vo.admin.*;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

import static com.design.working.enums.OperationEnum.*;

/**
 * @author gavin
 * @date 2022/5/18 10:24
 */
public class AdminDAOImpl implements AdminDAO {

    /**
     * 批准, 1
     */
    private static final int APPROVED = 1;

    /**
     * 拒绝（负数表示）, -1
     */
    private static final int REJECT_IN_MINUS = -1;

    /**
     * 离岗, -1
     */
    private static final int LEAVE_WORK_MINUS = -1;

    /**
     * 在岗, 1
     */
    private static final int WORKING = 1;

    /**
     * 查找未处理的学生工作申请记录
     *
     * @param page  分页参数
     * @param limit 每页条数
     * @return 未处理学生的工作申请记录
     * SQL查询异常
     */
    @Override
    public List<AdminHandleStudentWorkApplicationVO> selectStudentWorkApplication(Integer page, Integer limit) {

        List<AdminHandleStudentWorkApplicationVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student_work_application.id, m.name, c2.class_name, student_uid, s.username, dept.name, j.name, " +
                    "student_work_application.student_msg_template_id " +
                    "FROM student_work_application " +
                    "JOIN student_msg_template on student_work_application.student_msg_template_id = student_msg_template.id " +
                    "JOIN student s on student_msg_template.student_uid = s.uid " +
                    "JOIN classes c2 on student_msg_template.class_id = c2.id " +
                    "JOIN major m on c2.major_id = m.id " +
                    "JOIN job j on student_work_application.job_id = j.id " +
                    "JOIN dept ON j.dept_id = dept.id " +
                    "WHERE student_work_application.is_counselor_passed <> 0 " +
                    "AND student_work_application.is_dept_passed <> 0 " +
                    "AND student_work_application.is_admin_passed = 0 " +
                    "AND student_work_application.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String majorClass = resultSet.getString(2) + "-" + resultSet.getString(3);
                list.add(new AdminHandleStudentWorkApplicationVO(resultSet.getInt(1),
                        majorClass,
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getInt(8)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 插入管理员对学生申请记录的审批内容
     *
     * @param adminId                          管理员id
     * @param adminHandleStudentApplicationDTO 管理员对学生申请记录的审批内容
     *                                         SQL插入异常
     */
    @Override
    public void insertStudentApplicationHanding(Integer adminId, AdminHandleStudentApplicationDTO adminHandleStudentApplicationDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            connection.setAutoCommit(false);
            String sql = "UPDATE student_work_application SET admin_id = ?, admin_text = ?, " +
                    "is_admin_passed = ? " +
                    "WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, adminId);
            preparedStatement.setString(2, adminHandleStudentApplicationDTO.getAdvice());
            preparedStatement.setInt(3, adminHandleStudentApplicationDTO.getOperation());
            preparedStatement.setInt(4, adminHandleStudentApplicationDTO.getId());
            preparedStatement.executeUpdate();

            String sql1 = "SELECT student_work_application.job_id, remain, smt.student_uid " +
                    "FROM student_work_application " +
                    "JOIN job_remain jr on student_work_application.job_id = jr.job_id " +
                    "JOIN student_msg_template smt on student_work_application.student_msg_template_id = smt.id " +
                    "WHERE student_work_application.id = ? " +
                    "AND student_work_application.is_deleted = 0";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, adminHandleStudentApplicationDTO.getId());
            resultSet = preparedStatement.executeQuery();
            int jobId = NO_INDEX.getVal();
            // 岗位余量
            int remain = FULL_REMAIN.getVal();
            if (resultSet.next()) {
                jobId = resultSet.getInt(1);
                remain = resultSet.getInt(2);
            }
            // 学生uid
            int uid = resultSet.getInt(3);

            // 如果批准
            if (adminHandleStudentApplicationDTO.getOperation().equals(APPROVED)) {
                // 插入学生在岗记录(student_to_work)
                String sql2 = "INSERT INTO student_to_work(student_uid, job_id, is_admin_passed, working_time, status) " +
                        "VALUES(?, ?, ?, ?, ?);";
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setInt(1, uid);
                preparedStatement.setInt(2, jobId);
                preparedStatement.setInt(3, APPROVED);
                preparedStatement.setString(4, adminHandleStudentApplicationDTO.getWorkTime());
                preparedStatement.setInt(5, WORKING);
                preparedStatement.executeUpdate();
            }

            // 如果被拒绝，释放岗位余量
            if (adminHandleStudentApplicationDTO.getOperation().equals(REJECT.getVal())) {
                // 释放岗位余量
                if (remain >= NO_REMAIN.getVal()) {
                    remain += 1;
                }
                String sql3 = "UPDATE job_remain SET remain = ?, admin_id = ? " +
                        "WHERE job_id = ? AND is_deleted = 0";
                preparedStatement = connection.prepareStatement(sql3);
                preparedStatement.setInt(1, remain);
                preparedStatement.setInt(2, adminId);
                preparedStatement.setInt(3, jobId);
                preparedStatement.executeUpdate();
                JDBCUtil.close(preparedStatement, connection);
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 通过工号查找管理员在数据库中id
     *
     * @param uid 管理员工号
     * @return 管理员在数据库中id
     */
    @Override
    public int selectAdminId(Integer uid) {

        int id = NO_INDEX.getVal();
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id " +
                    "FROM admin " +
                    "WHERE uid = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return id;
    }

    /**
     * 查找申请表中学生申请工作填写的个人信息
     *
     * @param smtId 学生申请工作表中对应的个人信息记录id
     * @return 学生申请工作填写的个人信息
     * SQL查询异常
     */
    @Override
    public StudentApplicationDetailPO selectStudentApplicationDetail(Integer smtId) {

        StudentApplicationDetailPO studentApplicationDetailPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT phone, email, political_status, needy_student, dorm, strength, free_time " +
                    "FROM student_msg_template " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, smtId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationDetailPO = new StudentApplicationDetailPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationDetailPO;
    }

    /**
     * 查找辅导员和用工部门对学生申请表的意见
     *
     * @param swaId 学生申请工作表id
     * @return 辅导员和用工部门对学生申请表的意见
     * SQL查询异常
     */
    @Override
    public AdminPreAdvicePO selectStudentPreAdvice(Integer swaId) {

        AdminPreAdvicePO adminPreAdvicePO = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed, de.username, dept_text, is_dept_passed " +
                    "FROM student_work_application AS s " +
                    "JOIN counselor c on s.counselor_id = c.id " +
                    "JOIN dept_employee de on s.dept_employee_id = de.id " +
                    "WHERE s.id = ? " +
                    "AND s.is_deleted = 0;";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                adminPreAdvicePO = new AdminPreAdvicePO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return adminPreAdvicePO;
    }

    /**
     * 查询学生岗位变动申请
     *
     * @param page  分页参数
     * @param limit 每页条数
     * @return 学生岗位变动申请的条数
     * SQL查询异常
     */
    @Override
    public List<AdminHandleStudentWorkMotionPO> selectStudentWorkMotion(Integer page, Integer limit) {

        List<AdminHandleStudentWorkMotionPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT swm.id, c2.name, m.name, c.class_name, student_msg.uid, student.username, j.name, swm.work_type, swm.student_explanation " +
                    "FROM student_work_motion swm " +
                    "JOIN student_to_work stw on swm.student_to_work_id = stw.id " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept_employee de on j.dept_employee_id = de.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN student_msg on stw.student_uid = student_msg.uid " +
                    "JOIN student on student_msg.uid = student.uid " +
                    "JOIN classes c on student_msg.class_id = c.id " +
                    "JOIN major m on c.major_id = m.id " +
                    "JOIN college c2 on m.college_id = c2.id " +
                    "WHERE swm.is_admin_passed = 0 " +
                    "AND swm.is_dept_passed <> 0 " +
                    "AND swm.is_deleted = 0";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

                list.add(new AdminHandleStudentWorkMotionPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getInt(8),
                        resultSet.getString(9)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查找用工部门对学生岗位变更表的意见
     *
     * @param id 学生岗位变更表id
     * @return 用工部门对学生岗位变更表的意见
     * SQL查询异常
     */
    @Override
    public AdminPreAdvicePO selectDeptPreAdvice(Integer id) {

        AdminPreAdvicePO adminPreAdvicePO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT de.username, swm.dept_explanation, swm.is_dept_passed " +
                    "FROM student_work_motion swm " +
                    "JOIN dept_employee de on swm.dept_employee_id = de.id " +
                    "WHERE swm.id = ? " +
                    "AND swm.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                adminPreAdvicePO = new AdminPreAdvicePO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return adminPreAdvicePO;
    }

    /**
     * 插入管理员处理的学生岗位变更表意见
     *
     * @param adminId                     管理员id
     * @param adminHandleStudentMotionDTO 前端传来的表单数据
     */
    @Override
    public void insertStudentMotionHanding(Integer adminId, AdminHandleStudentMotionDTO adminHandleStudentMotionDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            connection.setAutoCommit(false);
            String sql = "UPDATE student_work_motion SET admin_id = ?, admin_explanation = ?, is_admin_passed = ? " +
                    "WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, adminId);
            preparedStatement.setString(2, adminHandleStudentMotionDTO.getAdvice());
            preparedStatement.setInt(3, adminHandleStudentMotionDTO.getOperation());
            preparedStatement.setInt(4, adminHandleStudentMotionDTO.getId());
            preparedStatement.executeUpdate();

            int jobId = NO_INDEX.getVal();
            // 岗位余量
            int remain = FULL_REMAIN.getVal();

            int studentToWorkId = NO_INDEX.getVal();

            String sql1 = "SELECT jr.job_id, jr.remain, swm.student_to_work_id " +
                    "FROM student_work_motion swm " +
                    "JOIN student_to_work stw on swm.student_to_work_id = stw.id " +
                    "JOIN job_remain jr on stw.job_id = jr.job_id " +
                    "WHERE swm.id = ? AND swm.is_deleted = 0";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, adminHandleStudentMotionDTO.getId());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                jobId = resultSet.getInt(1);
                remain = resultSet.getInt(2);
                studentToWorkId = resultSet.getInt(3);
            }

            // 如果同意换岗、离岗，释放岗位余量
            String sql2 = "UPDATE job_remain SET remain = ?, admin_id = ? " +
                    "WHERE job_id = ? AND is_deleted = 0";
            preparedStatement = connection.prepareStatement(sql2);
            if (adminHandleStudentMotionDTO.getOperation().equals(APPROVED)) {
                // 释放岗位余量
                if (remain >= NO_REMAIN.getVal()) {
                    remain += 1;
                }
                preparedStatement.setInt(1, remain);
                preparedStatement.setInt(2, adminId);
                preparedStatement.setInt(3, jobId);
                preparedStatement.executeUpdate();
            }
            String sql3 = "UPDATE student_to_work SET status = ?, is_motion = 0 WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setInt(1, LEAVE_WORK_MINUS);
            preparedStatement.setInt(2, studentToWorkId);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 查找正在工作的学生和岗位信息
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 查询到的在岗学生和岗位信息
     */
    @Override
    public List<AdminStudentInWorkingPO> selectAllWorking(Integer page, Integer limit) {
        List<AdminStudentInWorkingPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT stw.id, student.uid, student.username, ct.year, ct.term, d.name, j.name " +
                    "FROM student_to_work stw " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "WHERE stw.status = 1 " +
                    "AND stw.is_deleted = 0";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new AdminStudentInWorkingPO(resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查找正在工作的学生和岗位信息（通过搜索信息）
     *
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param uid         学生学号
     * @param studentName 学生姓名
     * @return 查询到的在岗学生和岗位信息
     */
    @Override
    public List<AdminStudentInWorkingPO> selectAllWorkingByName(Integer page, Integer limit, String uid, String studentName) {

        List<AdminStudentInWorkingPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT stw.id, student.uid, student.username, ct.year, ct.term, d.name, j.name " +
                    "FROM student_to_work stw " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "WHERE stw.status = 1 " +
                    "AND stw.is_deleted = 0 " +
                    "AND INSTR(student.uid, ?) > 0 " +
                    "AND INSTR(student.username, ?) > 0";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, studentName);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new AdminStudentInWorkingPO(resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * （在岗学生管理）变更岗位 - 强制将在岗学生下岗
     *
     * @param id      student_to_work的id
     * @param adminId 管理员id
     */

    @Override
    public void forceResignStudent(Integer id, Integer adminId) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            // 启用事务
            connection.setAutoCommit(false);
            String sql = "UPDATE student_to_work SET status = -1 WHERE id = ? AND is_deleted = 0";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();

            // 获取job_id
            String sql1 = "SELECT job_id " +
                    "FROM student_to_work " +
                    "WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            int jobId = NO_INDEX.getVal();
            if (resultSet.next()) {
                jobId = resultSet.getInt(1);
            }

            // 查询岗位容量
            String sql2 = "SELECT remain " +
                    "FROM job_remain " +
                    "WHERE job_id = ? FOR UPDATE";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, jobId);
            resultSet = preparedStatement.executeQuery();

            int jobRemain = NO_REMAIN.getVal();
            if (resultSet.next()) {
                jobRemain = resultSet.getInt(1);
                jobRemain += 1;
            }

            // 释放岗位
            String sql3 = "UPDATE job_remain " +
                    "SET remain = ?, admin_id = ? " +
                    "WHERE job_id = ?";
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setInt(1, jobRemain);
            preparedStatement.setInt(2, adminId);
            preparedStatement.setInt(3, jobId);
            preparedStatement.executeUpdate();

            connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 查询学生岗位分配记录
     *
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param uid         学生学号
     * @param studentName 学生姓名
     * @return 岗位分配记录
     */
    @Override
    public List<StudentWorkDistributionRecordPO> selectWorkDistributionRecord(Integer page, Integer limit, String uid, String studentName) {

        List<StudentWorkDistributionRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student.uid, student.username, ct.year, ct.term, d.name, j.name, stw.status " +
                    "FROM student_to_work stw " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "WHERE stw.is_admin_passed = 1 " +
                    "AND stw.is_deleted = 0 " +
                    "AND INSTR(student.uid, ?) > 0 " +
                    "AND INSTR(student.username, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, studentName);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                list.add(new StudentWorkDistributionRecordPO(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getInt(7)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生考勤记录（带时间）
     *
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param uid         学生学号
     * @param studentName 学生姓名
     * @param beginDate   开始日期
     * @param endDate     结束日期
     * @param type        状态（1：查询在岗学生；其他：查询全部学生）
     * @return 学生考勤记录
     */
    @Override
    public List<AdminStudentAttendanceRecordPO> selectStudentAttendanceByTime(Integer page, Integer limit, String uid, String studentName, String beginDate, String endDate, Integer type) {

        List<AdminStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student.uid, student.username, ct.year, ct.term, d.name, j.name, de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "WHERE INSTR(student.uid, ?) > 0 " +
                    "AND INSTR(student.username, ?) > 0 " +
                    "AND swa.working_date between ? and ? " +
                    "AND swa.is_deleted = 0";

            // 在岗学生
            if (type == WORKING) {
                sql += " AND stw.status = 1";
            }

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, studentName);
            preparedStatement.setString(3, beginDate);
            preparedStatement.setString(4, endDate);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(5, page);
                preparedStatement.setInt(6, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

                list.add(new AdminStudentAttendanceRecordPO(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生考勤记录（不带时间）
     *
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param uid         学生学号
     * @param studentName 学生姓名
     * @param type        状态（1：查询在岗学生；其他：查询全部学生）
     * @return 学生考勤记录
     */
    @Override
    public List<AdminStudentAttendanceRecordPO> selectStudentAttendance(Integer page, Integer limit, String uid, String studentName, Integer type) {
        List<AdminStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student.uid, student.username, ct.year, ct.term, d.name, j.name, de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "WHERE INSTR(student.uid, ?) > 0 " +
                    "AND INSTR(student.username, ?) > 0 " +
                    "AND swa.is_deleted = 0";

            // 在岗学生
            if (type == WORKING) {
                sql += " AND stw.status = 1";
            }

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, studentName);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

                list.add(new AdminStudentAttendanceRecordPO(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生相关信息
     *
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param uid         学生学号
     * @param studentName 学生姓名
     * @return 学生相关信息
     */
    @Override
    public List<StudentCommonVO> selectStudent(Integer page, Integer limit, String uid, String studentName) {

        List<StudentCommonVO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c2.name, m.name, c.class_name, s.uid, s.username " +
                    "FROM student s " +
                    "JOIN student_msg sm on s.uid = sm.uid " +
                    "JOIN classes c on sm.class_id = c.id " +
                    "JOIN major m on m.id = c.major_id " +
                    "JOIN college c2 on c2.id = m.college_id " +
                    "WHERE INSTR(s.uid, ?) > 0 " +
                    "AND INSTR(s.username, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, studentName);
            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String major = resultSet.getString(2) + "-" + resultSet.getString(3);
                list.add(new StudentCommonVO(resultSet.getString(1),
                        major,
                        resultSet.getInt(4),
                        resultSet.getString(5)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生详细信息
     *
     * @param uid 学生学号
     * @return 学生个人详细信息
     */
    @Override
    public StudentMsgPO selectStudentMsg(Integer uid) {

        StudentMsgPO studentMsgPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT gender, phone, email, political_status, is_needy_student, dorm, strength " +
                    "FROM student_msg " +
                    "WHERE uid = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentMsgPO = new StudentMsgPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentMsgPO;
    }

    /**
     * 查询学生申请岗位的信息
     *
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param uid         学生学号
     * @param studentName 学生姓名
     * @return 学生申请岗位的信息
     */
    @Override
    public List<StudentWorkApplicationAllPO> selectStudentApplicationRecord(Integer page, Integer limit, String uid, String studentName) {

        List<StudentWorkApplicationAllPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, d.name, j.name, swa.is_admin_passed, j.detail, swa.id, smt.id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt on swa.student_msg_template_id = smt.id " +
                    "JOIN student s on s.uid = smt.student_uid " +
                    "JOIN job j on swa.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "WHERE INSTR(s.uid, ?) > 0 " +
                    "AND INSTR(s.username, ?) > 0 " +
                    "AND swa.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, studentName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new StudentWorkApplicationAllPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getInt(9),
                        resultSet.getInt(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生岗位申请的记录
     *
     * @param swaId 学生岗位申请表id
     * @return 学生岗位申请审批记录
     */
    @Override
    public StudentApplicationExaminationPO selectStudentApplicationExamination(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed, " +
                    "de.username, dept_text, is_dept_passed, a.username, admin_text, is_admin_passed " +
                    "FROM student_work_application AS s " +
                    "JOIN counselor c on s.counselor_id = c.id " +
                    "JOIN admin a on s.admin_id = a.id " +
                    "JOIN dept_employee de on s.dept_employee_id = de.id " +
                    "WHERE s.id = ? " +
                    "AND s.is_deleted = 0;";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询岗位工资
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 岗位工资信息
     */
    @Override
    public List<SelectJobSalaryPO> selectJobSalary(Integer page, Integer limit) {

        List<SelectJobSalaryPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT job.id, ct.year, ct.term, d.name, job.name, job.salary " +
                    "FROM job " +
                    "JOIN campus_term ct on job.campus_term_id = ct.id " +
                    "JOIN dept d on job.dept_id = d.id";
            //创建过的岗位应该都能查询到工资
//                "WHERE job.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(new SelectJobSalaryPO(resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getBigDecimal(6)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 修改岗位工资
     *
     * @param id        岗位id
     * @param newSalary 新的工资
     */
    @Override
    public void updateJobSalaryById(Integer id, BigDecimal newSalary) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE job SET salary = ? WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setBigDecimal(1, newSalary);
            preparedStatement.setInt(2, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 查询学生工资
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 学生工资信息
     */
    @Override
    public List<SelectStudentSalaryPO> selectStudentSalary(Integer page, Integer limit) {

        List<SelectStudentSalaryPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, d.name, j.name, sws.salary_date, sws.salary, sws.is_salary_settled " +
                    "FROM student_work_salaries sws " +
                    "JOIN student_to_work stw on stw.id = sws.student_to_work_id " +
                    "JOIN student s on s.uid = stw.student_uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "WHERE sws.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(new SelectStudentSalaryPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getDate(7).toLocalDate(),
                        resultSet.getBigDecimal(8),
                        resultSet.getInt(9)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询学生工资
     *
     * @param page      分页页数
     * @param limit     每页数据条数
     * @param searchDTO 搜索的信息
     * @return 对应的学生工资信息
     */
    @Override
    public List<SelectStudentSalaryPO> selectStudentSalaryByName(Integer page, Integer limit, SearchDTO searchDTO) {

        List<SelectStudentSalaryPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, d.name, j.name, sws.salary_date, sws.salary, sws.is_salary_settled " +
                    "FROM student_work_salaries sws " +
                    "JOIN student_to_work stw on stw.id = sws.student_to_work_id " +
                    "JOIN student s on s.uid = stw.student_uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "WHERE sws.is_deleted = 0 " +
                    "AND INSTR(s.uid, ?) > 0 " +
                    "AND INSTR(s.username, ?) > 0 " +
                    "AND SWS.salary_date between ? and ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, searchDTO.getId());
            preparedStatement.setString(2, searchDTO.getName());
            preparedStatement.setString(3, searchDTO.getBeginDate());
            preparedStatement.setString(4, searchDTO.getEndDate());

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(5, page);
                preparedStatement.setInt(6, limit);
            }
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(new SelectStudentSalaryPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getDate(7).toLocalDate(),
                        resultSet.getBigDecimal(8),
                        resultSet.getInt(9))
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询所有待审批的部门申请岗位
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 所有待审批的部门申请岗位
     */
    @Override
    public List<SelectDeptJobApplicationPO> selectDeptJobApplication(Integer page, Integer limit) {

        List<SelectDeptJobApplicationPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dja.id, d.name, dja.job_name, ct.year, ct.term " +
                    "FROM dept_job_application dja " +
                    "JOIN campus_term ct on dja.campus_term_id = ct.id " +
                    "JOIN dept_employee de on dja.dept_employee_id = de.id " +
                    "JOIN dept_employee_msg dem on de.uid = dem.uid " +
                    "JOIN dept d on dem.dept_id = d.id " +
                    "WHERE dja.admin_type = 0 " +
                    "AND dja.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new SelectDeptJobApplicationPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询部门申请岗位的详细信息
     *
     * @param id dept_job_application表的id
     * @return 相关的详细信息
     */
    @Override
    public DeptJobApplicationDetailVO selectDeptJobApplicationDetail(Integer id) {

        DeptJobApplicationDetailVO deptJobApplicationDetailVO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT de.username, dja.job_detail, dja.place, dja.need_count " +
                    "FROM dept_job_application dja " +
                    "join dept_employee de on dja.dept_employee_id = de.id " +
                    "WHERE dja.id = ? " +
                    "AND dja.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                deptJobApplicationDetailVO = new DeptJobApplicationDetailVO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptJobApplicationDetailVO;
    }

    /**
     * 批准部门申请岗位（事务控制）
     *
     * @param handleDeptJobApplicationBO  dept_job_application相关数据
     * @param handleDeptJobApplicationDTO 表单数据
     */
    @Override
    public void approveDeptJobApplication(HandleDeptJobApplicationBO handleDeptJobApplicationBO,
                                          HandleDeptJobApplicationDTO handleDeptJobApplicationDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            // 开启事务
            connection.setAutoCommit(false);

            // 将dto录入dept_job_application中
            String sql1 = "UPDATE dept_job_application SET admin_id = ?, admin_text = ?, admin_type = 1 " +
                    "WHERE id = ?;";
            preparedStatement = connection.prepareStatement(sql1);

            preparedStatement.setInt(1, handleDeptJobApplicationDTO.getUid());
            preparedStatement.setString(2, handleDeptJobApplicationDTO.getAdvice());
            preparedStatement.setInt(3, handleDeptJobApplicationDTO.getId());

            preparedStatement.executeUpdate();

            //在job中创建岗位
            String sql2 = "INSERT INTO job(campus_term_id, name, dept_id, dept_employee_id," +
                    " job_category_id, place, detail, salary, admin_id) " +
                    "VALUE(?, ?, ?, ?, ?, ?, ?, ?, ?);";
            preparedStatement = connection.prepareStatement(sql2, PreparedStatement.RETURN_GENERATED_KEYS);

            preparedStatement.setInt(1, handleDeptJobApplicationBO.getCampusTermId());
            preparedStatement.setString(2, handleDeptJobApplicationBO.getJobName());
            preparedStatement.setInt(3, handleDeptJobApplicationBO.getDeptId());
            preparedStatement.setInt(4, handleDeptJobApplicationBO.getDeptEmployeeId());
            preparedStatement.setInt(5, handleDeptJobApplicationBO.getJobCategoryId());
            preparedStatement.setString(6, handleDeptJobApplicationBO.getPlace());
            preparedStatement.setString(7, handleDeptJobApplicationBO.getJobDetail());
            preparedStatement.setBigDecimal(8, handleDeptJobApplicationBO.getSalary());
            preparedStatement.setInt(9, handleDeptJobApplicationBO.getAdminId());

            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            int newJobId = NO_INDEX.getVal();
            if (resultSet.next()) {
                newJobId = resultSet.getInt(1);
            }
            // 在job_reamin中创建岗位容量
            String sql3 = "INSERT INTO job_remain(job_id, contain, remain, admin_id) " +
                    "VALUES(?, ?, ?, ?);";
            preparedStatement = connection.prepareStatement(sql3);

            preparedStatement.setInt(1, newJobId);
            preparedStatement.setInt(2, handleDeptJobApplicationBO.getNeedCount());
            preparedStatement.setInt(3, handleDeptJobApplicationBO.getNeedCount());
            preparedStatement.setInt(4, handleDeptJobApplicationBO.getAdminId());

            preparedStatement.executeUpdate();

            // 提交事务
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 拒绝部门申请岗位
     *
     * @param handleDeptJobApplicationDTO 表单数据
     */
    @Override
    public void rejectDeptJobApplication(HandleDeptJobApplicationDTO handleDeptJobApplicationDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE dept_job_application " +
                    "SET admin_id = ?, admin_text = ?, admin_type = -1 " +
                    "WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, handleDeptJobApplicationDTO.getUid());
            preparedStatement.setString(2, handleDeptJobApplicationDTO.getAdvice());
            preparedStatement.setInt(3, handleDeptJobApplicationDTO.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 通过部门员工id查询其所在部门的id
     *
     * @param id dept_employee的id
     * @return dept的id
     */
    @Override
    public int selectDeptIdFromDeptEmployeeId(Integer id) {

        int deptId = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dem.dept_id " +
                    "FROM dept_employee_msg dem " +
                    "JOIN dept_employee de on dem.uid = de.uid " +
                    "WHERE de.id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                deptId = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptId;
    }

    /**
     * 查询大部分dept_job_application的信息
     *
     * @param id dept_job_application的id
     * @return 相关信息
     */
    @Override
    public SelectTotalDeptJobApplicationPO selectTotalDeptJobApplication(Integer id) {

        SelectTotalDeptJobApplicationPO selectTotalDeptJobApplicationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dept_employee_id, job_name, place, job_detail, need_count, " +
                    "job_category_id, campus_term_id " +
                    "FROM dept_job_application " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0;";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                selectTotalDeptJobApplicationPO = new SelectTotalDeptJobApplicationPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return selectTotalDeptJobApplicationPO;
    }

    /**
     * 查找所有待审核的部门岗位变动记录
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 所有待审核的岗位变动记录
     */
    @Override
    public List<SelectDeptJobMotionPO> selectDeptJobMotion(Integer page, Integer limit) {

        List<SelectDeptJobMotionPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT djm.id, d.name, j.name, ct.year, ct.term, djm.dept_explanation, djm.job_type " +
                    "FROM dept_job_motion djm " +
                    "JOIN job j on djm.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "WHERE djm.is_admin_passed = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new SelectDeptJobMotionPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getInt(7)
                ));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 处理部门岗位变动申请--删除岗位
     *
     * @param adminHandleDeptJobWorkMotionDTO 需要处理的数据
     * @param jobId                           岗位id
     */
    @Override
    public void insertDeptJobWorkMotionHandlingIfDeleteJob(AdminHandleDeptJobWorkMotionDTO adminHandleDeptJobWorkMotionDTO, Integer jobId) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            // 开启事务
            connection.setAutoCommit(false);

            // 1. 更新dept_job_motion中管理员处理的信息
            String sql = "UPDATE dept_job_motion SET admin_id = ?, admin_explanation = ?, is_admin_passed = ? " +
                    "WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, adminHandleDeptJobWorkMotionDTO.getUid());
            preparedStatement.setString(2, adminHandleDeptJobWorkMotionDTO.getAdvice());
            preparedStatement.setInt(3, adminHandleDeptJobWorkMotionDTO.getOperation());
            preparedStatement.setInt(4, adminHandleDeptJobWorkMotionDTO.getId());

            preparedStatement.executeUpdate();

            // 先强制结岗所有在该岗的学生（其实只有岗位没有学生了才能删除岗位），再将其在job中删除
            // 2. 将学生结岗
            String sql2 = "UPDATE student_to_work SET status = 0 WHERE job_id = ?";

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, jobId);
            preparedStatement.executeUpdate();

            // 3. 删除岗位（软删除）
            String sql3 = "UPDATE job SET is_deleted = 1 WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setInt(1, jobId);
            preparedStatement.executeUpdate();

            // 4. 清空岗位容量
            String sql4 = "UPDATE job_remain SET remain = 0, admin_id = ? WHERE job_id = ?";

            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setInt(1, adminHandleDeptJobWorkMotionDTO.getUid());
            preparedStatement.setInt(2, jobId);
            preparedStatement.executeUpdate();

            // 提交事务
            connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 处理部门岗位变动申请--拒绝申请
     *
     * @param adminHandleDeptJobWorkMotionDTO 需要处理的数据
     */
    @Override
    public void insertDeptJobWorkMotionHandlingIfReject(AdminHandleDeptJobWorkMotionDTO adminHandleDeptJobWorkMotionDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE dept_job_motion SET admin_id = ?, admin_explanation = ?, is_admin_passed = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, adminHandleDeptJobWorkMotionDTO.getUid());
            preparedStatement.setString(2, adminHandleDeptJobWorkMotionDTO.getAdvice());
            preparedStatement.setInt(3, REJECT_IN_MINUS);

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 通过dept_job_motion查找对应job_id
     *
     * @param deptJobMotionId dept_job_motion的id
     * @return 对应的job_id
     */
    @Override
    public int selectJobIdFromDeptJobMotion(Integer deptJobMotionId) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        int jobId = 0;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT job_id FROM dept_job_motion WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptJobMotionId);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                jobId = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return jobId;
    }

    /**
     * 查询全部岗位申请记录
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 全部岗位申请记录
     */
    @Override
    public List<DeptJobApplicationRecordPO> selectDeptJobApplicationRecord(Integer page, Integer limit) {

        List<DeptJobApplicationRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dja.id, ct.year, ct.term, d.name, dja.job_name, dja.admin_type " +
                    "FROM dept_job_application dja " +
                    "JOIN campus_term ct on dja.campus_term_id = ct.id " +
                    "JOIN dept d on dja.dept_id = d.id " +
                    "WHERE dja.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(new DeptJobApplicationRecordPO(
                        resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 关键字查询岗位申请记录
     *
     * @param page                        分页页数
     * @param limit                       每页数据条数
     * @param searchDeptJobApplicationDTO 搜索的数据
     * @return 相关的岗位申请记录
     */
    @Override
    public List<DeptJobApplicationRecordPO> selectDirectDeptJobApplicationRecord(Integer page, Integer limit, SearchDeptJobApplicationDTO searchDeptJobApplicationDTO) {

        List<DeptJobApplicationRecordPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dja.id, ct.year, ct.term, d.name, dja.job_name, dja.admin_type " +
                    "FROM dept_job_application dja " +
                    "JOIN campus_term ct on dja.campus_term_id = ct.id " +
                    "JOIN dept d on dja.dept_id = d.id " +
                    "WHERE dja.is_deleted = 0 " +
                    "AND INSTR(name, ?) > 0 " +
                    "AND INSTR(year, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, searchDeptJobApplicationDTO.getDeptName());
            preparedStatement.setInt(2, searchDeptJobApplicationDTO.getStartYear());

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(new DeptJobApplicationRecordPO(
                        resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6)
                ));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }

        return list;
    }

    /**
     * 查询岗位申请记录的审批详情
     *
     * @param id dept_job_application的id
     * @return 相关审批详情
     */
    @Override
    public DeptJobApplicationExaminationVO selectDeptJobApplicationExamination(Integer id) {

        DeptJobApplicationExaminationVO deptJobApplicationExaminationVO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT a.username, dja.admin_text " +
                    "FROM dept_job_application dja " +
                    "JOIN admin a on dja.admin_id = a.id " +
                    "WHERE dja.id = ? " +
                    "AND dja.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                deptJobApplicationExaminationVO = new DeptJobApplicationExaminationVO(
                        resultSet.getString(1),
                        resultSet.getString(2)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptJobApplicationExaminationVO;
    }

    /**
     * 查询全部用工评价记录
     *
     * @param page  分页页数
     * @param limit 每页数据条数
     * @return 全部用工评价记录
     */
    @Override
    public List<WorkEvaluationPO> selectWorkEvaluation(Integer page, Integer limit) {

        List<WorkEvaluationPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT des.id, s.uid, s.username, ct.year, ct.term, d.name, job.name " +
                    "FROM dept_evaluate_student des " +
                    "JOIN dept_employee de on des.dept_employee_id = de.id " +
                    "JOIN student_to_work stw on des.student_to_work_id = stw.id " +
                    "JOIN student s on stw.student_uid = s.uid " +
                    "JOIN job on stw.job_id = job.id " +
                    "JOIN campus_term ct on ct.id = job.campus_term_id " +
                    "JOIN dept d on job.dept_id = d.id " +
                    "WHERE des.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(1, page);
                preparedStatement.setInt(2, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new WorkEvaluationPO(
                        resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                ));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过关键词查询部分用工评价记录
     *
     * @param page                    分页页数
     * @param limit                   每页数据条数
     * @param searchWorkEvaluationDTO 搜索框传入的相关参数
     * @return 部分用工评价记录
     */
    @Override
    public List<WorkEvaluationPO> selectWorkEvaluationByName(Integer page, Integer limit, SearchWorkEvaluationDTO searchWorkEvaluationDTO) {

        List<WorkEvaluationPO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT des.id, s.uid, s.username, ct.year, ct.term, d.name, job.name " +
                    "FROM dept_evaluate_student des " +
                    "JOIN dept_employee de on des.dept_employee_id = de.id " +
                    "JOIN student_to_work stw on des.student_to_work_id = stw.id " +
                    "JOIN student s on stw.student_uid = s.uid " +
                    "JOIN job on stw.job_id = job.id " +
                    "JOIN campus_term ct on ct.id = job.campus_term_id " +
                    "JOIN dept d on job.dept_id = d.id " +
                    "WHERE des.is_deleted = 0 " +
                    "AND INSTR(s.uid, ?) > 0 " +
                    "AND INSTR(s.username, ?) > 0 " +
                    "AND INSTR(d.name, ?) > 0 ";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, searchWorkEvaluationDTO.getUid());
            preparedStatement.setString(2, searchWorkEvaluationDTO.getName());
            preparedStatement.setString(3, searchWorkEvaluationDTO.getDeptName());

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(new WorkEvaluationPO(
                        resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过id查询其对应的用工评价详细信息
     *
     * @param id dept_evaluate_student的id
     * @return 相应的用工评价详细信息
     */
    @Override
    public WorkEvaluationDetailVO selectWorkEvaluationDetailById(Integer id) {

        WorkEvaluationDetailVO workEvaluationDetailVO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT de.username, des.evaluation " +
                    "FROM dept_evaluate_student des " +
                    "JOIN dept_employee de on des.dept_employee_id = de.id " +
                    "WHERE des.id = ? " +
                    "AND des.is_deleted = 0;";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                workEvaluationDetailVO = new WorkEvaluationDetailVO(
                        resultSet.getString(1),
                        resultSet.getString(2)
                );
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return workEvaluationDetailVO;
    }

    /**
     * 查看管理员是否审批学生岗位申请记录
     *
     * @param swaId student_work_application的id
     * @return 审批结果：0 - 未审批; (-1) - 拒绝; 1 - 通过
     */
    @Override
    public int isStudentApplicationExaminedByAdmin(Integer swaId) {

        int tar = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT is_admin_passed " +
                    "FROM student_work_application swa " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                tar = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return tar;
    }

    /**
     * 查看用工部门是否审批学生岗位申请记录
     *
     * @param swaId student_work_application的id
     * @return 审批结果：0 - 未审批; (-1) - 拒绝; 1 - 通过
     */
    @Override
    public int isStudentApplicationExaminedByDeptEmployee(Integer swaId) {
        int tar = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT is_dept_passed " +
                    "FROM student_work_application swa " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                tar = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return tar;
    }

    /**
     * 查看辅导员是否审批学生岗位申请记录
     *
     * @param swaId student_work_application的id
     * @return 审批结果：0 - 未审批; (-1) - 拒绝; 1 - 通过
     */
    @Override
    public int isStudentApplicationExaminedByCounselor(Integer swaId) {
        int tar = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT is_counselor_passed " +
                    "FROM student_work_application swa " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                tar = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return tar;
    }

    /**
     * 查询学生岗位申请的记录(辅导员处理的)
     *
     * @param swaId 学生岗位申请表id
     * @return 学生岗位申请审批记录
     */
    @Override
    public StudentApplicationExaminationPO selectStudentApplicationExaminationByCounselor(Integer swaId) {
        StudentApplicationExaminationPO studentApplicationExaminationPO = new StudentApplicationExaminationPO();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed " +
                    "FROM student_work_application AS s " +
                    "JOIN counselor c on s.counselor_id = c.id " +
                    "WHERE s.id = ? " +
                    "AND s.is_deleted = 0;";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO.setCounselor(resultSet.getString(1));
                studentApplicationExaminationPO.setCounselorText(resultSet.getString(2));
                studentApplicationExaminationPO.setCounselorPassed(resultSet.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询学生岗位申请的记录(用工部门处理的)
     *
     * @param swaId 学生岗位申请表id
     * @return 学生岗位申请审批记录
     */
    @Override
    public StudentApplicationExaminationPO selectStudentApplicationExaminationByDeptEmployee(Integer swaId) {
        StudentApplicationExaminationPO studentApplicationExaminationPO = new StudentApplicationExaminationPO();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT de.username, dept_text, is_dept_passed " +
                    "FROM student_work_application AS s " +
                    "JOIN dept_employeed e on s.dept_employee_id = de.id " +
                    "WHERE s.id = ? " +
                    "AND s.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO.setDeptEmployee(resultSet.getString(1));
                studentApplicationExaminationPO.setDeptEmployeeText(resultSet.getString(2));
                studentApplicationExaminationPO.setDeptEmployeePassed(resultSet.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 手动对单个学生结岗
     *
     * @param id student_to_work的id
     */
    @Override
    public void finishStudentWorkById(Integer id) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE student_to_work SET status = 0 WHERE id = ? AND is_deleted = 0";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 通过年月查询当月工资记录是否生成
     *
     * @param date 查询的年月
     * @return YES - 已生成；NO - 未生成
     */
    @Override
    public int selectSalaryStatusByDate(String date) {

        int tar = NO.getVal();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT salary_date " +
                    "FROM student_work_salaries " +
                    "WHERE INSTR(salary_date, ?) > 0 " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, date);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                tar = YES.getVal();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return tar;
    }

    /**
     * 将某月工资设置为已发放
     *
     * @param date 年月
     */
    @Override
    public void updateSalaryByDate(String date) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE student_work_salaries " +
                    "SET is_salary_settled = 1 " +
                    "WHERE salary_date = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, date);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 查询该年月有考勤的学生swaId和其岗位对应的工资
     *
     * @param date 年月
     * @return 该年月有考勤的学生swaId和其岗位对应的工资
     */
    @Override
    public Map<Integer, BigDecimal> selectStudentWorkByDate(String date) {

        Map<Integer, BigDecimal> map = new HashMap<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student_to_work_id, j.salary " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN job j on stw.job_id = j.id " +
                    "WHERE INSTR(working_date, ?) " +
                    "AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, date);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                map.put(resultSet.getInt(1), resultSet.getBigDecimal(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return map;
    }

    /**
     * 增加学生在某年月的工作记录（对应插入的salary_date应是工作的下一月）
     *
     * @param id     stwId
     * @param date   发工资的月份
     * @param salary 工作月的工资
     */
    @Override
    public void insertSalaryToStudentByDate(Integer id, String date, BigDecimal salary) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "INSERT INTO student_work_salaries(salary, salary_date, student_to_work_id) " +
                    "VALUES(?, ?, ?)";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setBigDecimal(1, salary);
            preparedStatement.setString(2, date);
            preparedStatement.setInt(3, id);

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 查询学生在某年月对应工作的出勤记录
     *
     * @param id   swaId
     * @param date 年月
     * @return swaId在对应年月的出勤记录
     */
    @Override
    public List<Integer> selectWorkAttendanceById(Integer id, String date) {

        List<Integer> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT is_attend " +
                    "FROM student_work_attendance swa " +
                    "WHERE INSTR(working_date, ?) " +
                    "AND student_to_work_id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, date);
            preparedStatement.setInt(2, id);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(resultSet.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 判断操作月是否已经统计并发放过工资
     *
     * @param date 年月
     * @return NO - 未统计并发放；YES - 已统计并发放
     */
    @Override
    public int isSendAndStatisticSalary(String date) {
        int tar = NO.getVal();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT salary_date " +
                    "FROM student_work_salaries " +
                    "WHERE INSTR(salary_date, ?) > 0 " +
                    "AND is_salary_settled = 1 " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, date);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                tar = YES.getVal();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return tar;
    }
}